﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace IsikFx.Project.WcfService.Helpers
{
    public class QueryConstants
    {
        public const string INSERT_NEW_USER = "INSERT INTO USERS (User_Name, User_Surname,TeamID,RoleType,DomainName) VALUES ('{0}','{1}',{2},{3},'{4}')";
        public const string CHECK_FIRST_LOGIN = "SELECT * FROM USERS WHERE DomainName = '{0}'";
        public const string INSERT_NEW_FUND = "INSERT INTO Fundings VALUES({0},'{1}',{2},{3},{4},0)";
        public const string SELECT_NOT_SHOWN_FUNDS = "SELECT CUSTOMERS.ID AS ID,USERS.User_Name AS UserName, USERS.User_Surname AS UserSurname, CUSTOMERS.FirstFundAmount AS Amount, CUSTOMERS.CurrencyCode AS CurrencyCode FROM CUSTOMERS INNER JOIN USERS ON CUSTOMERS.AgentID = Users.ID WHERE CUSTOMERS.PopupShown = 0";
        public const string UPDATE_SHOWN_FUNDS = "UPDATE Fundings SET  Shown = 1 WHERE ID = {0}";
        public const string SELECT_USERID = "SELECT ID FROM USERS WHERE DomainName = '{0}'";
        public const string UPDATE_USER_ROLE = "UPDATE USERS SET SalesOrRetantion= {0} WHERE ID= {1}";
        public const string SELECT_USER = "SELECT * FROM USERS WHERE DomainName = '{0}'";
        public const string INSERT_CUSTOMER = "INSERT INTO CUSTOMERS (AgentID, CustomerName, CustomerSurname,LoginNo,AccountOpenDate,FirstFundDate,FirstFundAmount,CurrencyCode,PopupShown) VALUES ({0},'{1}','{2}',{3},'{4}','{5}',{6},'{7}','{8}')";
        public const string SELECT_CUSTOMERS = "SELECT * FROM CUSTOMERS WHERE AgentID={0}";
        public const string SELECT_USERS_BY_ROLE = "SELECT * FROM USERS WHERE RoleType={0}";
        public const string UPDATE_FUND_SHOWN_STATUS = "UPDATE CUSTOMERS SET PopupShown=1 WHERE ID={0}";
        public const string UPDATE_GOALS = "UPDATE [Goals] SET [FundGoal] ={0},[AccountGoal] = {1},[Currency] = '{2}' WHERE [AgentID]={3}";
        public const string INSERT_GOALS = "INSERT INTO GOALS([AgentID], [FundGoal], [AccountGoal], [Currency]) VALUES( {0},{1},{2},'{3}' )";
        public const string SELECT_GOALS = "SELECT * FROM [Goals] WHERE [AgentID]={0}";
        public const string SELECT_GOAL_PROGRESS = "SELECT COUNT(*) AS [Count], SUM(FirstFundAmount) AS [Total] FROM Customers WHERE AgentID={0}";
        public const string SELECT_ADMIN_ANNOUNCEMETS = "SELECT * FROM Announcements WHERE AdminID={0} AND IsDeleted = 0";
        public const string SELECT_USER_ANNOUNCEMETS = "SELECT * FROM Announcements WHERE RoleType={0} AND IsDeleted = 0";
        public const string INSERT_NEW_ANNOUNCEMENT = "INSERT INTO Announcements (AdminID,AnnouncementID, AnnouncementTitle,Announcement,CreateDate,IsDeleted,RoleType) VALUES ({0},'{1}','{2}','{3}','{4}',0,{5})";
        public const string RESET_GOALS = "UPDATE [Goals] SET [FundGoal] =0,[AccountGoal] = 0,[Currency] = '{0}'";
        public const string SELECT_TOTAL_FUND_OF_USER = "SELECT SUM(FirstFundAmount) AS TotalFundAmount FROM Customers INNER JOIN Users On Customers.AgentID = Users.ID WHERE Users.ID={0}";
        public const string DELETE_ANNOUNCEMETS = "UPDATE [Announcements] SET [IsDeleted]=1 WHERE ID={0}";
        public const string SELECT_WATCHMEN = "SELECT * FROM [Watchmen] WHERE [WatchmanDate]='{0}'";
        public const string INSERT_WATCHMEN = "INSERT INTO [Watchmen] (UserID,AdminID,WatchmanName,WatchmanSurname,WatchmanDate) VALUES ({0},{1},'{2}','{3}','{4}')";
        public const string DELETE_WATCHMEN = "DELETE * FROM [Watchmen] WHERE [WatchmanDate]='{0}'";
        public const string INSERT_FUNDLOG= "INSERT INTO [FundLog] (UserID,FundAmount,IsFundOut,FundDate,FundTime,IsPopUpShown) VALUES ('{0}','{1}',{2},'{3}','{4}',0)";
        public const string SELECT_MINUS_FUNDS = "SELECT * FROM [FundLog] WHERE [UserID]='{0}' AND [FundDate]='{1}' AND [IsFundOut] = {2}";
        public const string SELECT_PLUS_FUNDS = "SELECT * FROM [FundLog] WHERE [UserID]='{0}' AND [FundDate]='{1}' AND [IsFundOut] ={2}";
        public const string SELECT_ALL_FUNDS = "SELECT * FROM [FundLog] WHERE [UserID]='{0}' AND [FundDate]='{1}'";
        
    }
}
